﻿using Dapper;
using OddUserManager.Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading;

namespace OddUserManager.Data
{
    public class MicroDataDapperRepository<T, TPrimaryKey>
    {
        private readonly string _connectionString;
        protected MicroDataDapperRepository()
        {
            _connectionString = DatabaseSettings.MacroMatchsDataConnectionString;
        }

        protected IDbConnection GetOpenConnection()
        {
            IDbConnection connection = new SqlConnection(_connectionString);
            try
            {
                connection.Open();
            }
            catch (InvalidOperationException)
            {
                Thread.Sleep(5000);
                connection.Dispose();
                connection = GetOpenConnection();
            }
            return connection;
        }





        protected int Execute(CommandType commandType, string sql, object parameters = null)
        {
            using (var connection = GetOpenConnection())
            {
                return connection.Execute(sql, parameters, commandType: commandType);
            }
        }

        protected static PropertyContainer ParseProperties(T obj)
        {
            var propertyContainer = new PropertyContainer();

            var typeName = typeof(T).Name;
            var validKeyNames = new[] { "Id", string.Format("{0}Id", typeName), string.Format("{0}_Id", typeName) };

            var properties = typeof(T).GetProperties();
            foreach (var property in properties)
            {
                // Skip reference types (but still include string!)
                if (property.PropertyType.IsClass && property.PropertyType != typeof(string))
                    continue;

                // Skip methods without a public setter
                if (property.GetSetMethod() == null)
                    continue;

                // Skip methods specifically ignored
                if (property.IsDefined(typeof(DapperIgnore), false))
                    continue;

                var name = property.Name;
                var value = typeof(T).GetProperty(property.Name).GetValue(obj, null);

                if (property.IsDefined(typeof(DapperKey), false) || validKeyNames.Contains(name))
                {
                    propertyContainer.AddId(name, value);
                }
                else
                {
                    propertyContainer.AddValue(name, value);
                }
            }

            return propertyContainer;
        }

        private static string GetSqlPairs(IEnumerable<string> keys, string separator = ", ")
        {
            var pairs = keys.Select(key => string.Format("{0}=@{0}", key)).ToList();
            return string.Join(separator, pairs);
        }



        public bool Insert(T entity)
        {
            bool isSuccess = false;
            var propertyContainer = ParseProperties(entity);
            var sql = $"insert into {typeof(T).Name} ({string.Join(", ", propertyContainer.ValueNames)}) values (@{string.Join(", @", propertyContainer.ValueNames)})";
            using (var connection = GetOpenConnection())
            {
                connection.Execute(sql, propertyContainer.ValuePairs);
                isSuccess = true;
            }
            return isSuccess;
        }

        public bool InsertBatch(IEnumerable<T> list)
        {
            bool isSuccess = false;
            if (null != list && list.Count() > 0)
            {
                var propertyContainer = ParseProperties(list.First());
                var sql = $"insert into {typeof(T).Name} ({string.Join(", ", propertyContainer.ValueNames)}) values (@{string.Join(", @", propertyContainer.ValueNames)})";
                using (var connection = GetOpenConnection())
                {
                    connection.Execute(sql, list, null, 3000, null);
                    isSuccess = true;
                }
            }
            return isSuccess;
        }



        public bool Update(T entity)
        {
            bool isSuccess = false;
            var propertyContainer = ParseProperties(entity);
            var sqlIdPairs = GetSqlPairs(propertyContainer.IdNames);
            var sqlValuePairs = GetSqlPairs(propertyContainer.ValueNames);
            var sql = $"update {typeof(T).Name} set {sqlValuePairs} where {sqlIdPairs}";
            Execute(CommandType.Text, sql, propertyContainer.AllPairs);
            isSuccess = true;
            return isSuccess;
        }



        public void Delete(T entity)
        {
            var propertyContainer = ParseProperties(entity);
            var sqlIdPairs = GetSqlPairs(propertyContainer.IdNames);
            var sql = $"delete from {typeof(T).Name}  where {sqlIdPairs}";
            Execute(CommandType.Text, sql, propertyContainer.IdPairs);
        }

        public void Delete(int id)
        {
            var sql = $"delete from {typeof(T).Name}  where Id={id}";
            Execute(CommandType.Text, sql);
        }


        public void Delete(string columnName, string value)
        {
            var sql = $"delete from {typeof(T).Name}  where {columnName}={value}";
            Execute(CommandType.Text, sql);
        }




        public void DeleteBatch(List<int> ids)
        {
            var sql = $"delete from {typeof(T).Name}  where Id in({string.Join(",", ids)})";
            Execute(CommandType.Text, sql);
        }

        public void Delete(TPrimaryKey id, string tableName, string IdColumnName)
        {
            var sqlIdPairs = "" + IdColumnName + "=" + id;
            var sql = $"delete from {tableName}  where {sqlIdPairs}";
            Execute(CommandType.Text, sql, null);
        }
    }

}
